﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.DTO
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports Camstar.WebClient.WebServicesProxy.InSiteWebServices

Public Class uMESMainViewBusiness
    Implements uMESMainViewIBusiness

    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

#Region "获取现场问题反馈"
    Function GetProblemInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetProblemInfo
        Dim strQuery As String = "SELECT DISTINCT up.ufcproblemname,c.containername,pb.productname,te.fullname AS tofullname," & vbCrLf & _
                                "ce.fullname AS createfullname,up.ufccreatedate,DECODE(up.ufcstatus,0,'已完成',1,'未处理',2,'处理中',3,'处理完') AS status,c.togetherflag" & vbCrLf & _
                                "FROM ufcproblem up" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = up.containerid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = up.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN employee te ON te.employeeid = up.ufctoemployeeid" & vbCrLf & _
                                "LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = up.factoryid" & vbCrLf & _
                                "WHERE up.ufcstatus = 1" & vbCrLf
        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND (f.factoryname = '" & para("FactoryName").ToString & "' OR f.factoryname IS NULL)" & vbCrLf
        End If

        strQuery += "ORDER BY up.ufccreatedate DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取暂停批次"
    Function GetHoldContainer(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetHoldContainer
        Dim strQuery As String = "SELECT c.containername,pb.productname,c.qty,sb.specname,hr.holdreasonname," & vbCrLf & _
                                "   c.plannedstartdate,c.plannedcompletiondate,c.onholddate,f.factoryname" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN factory f ON cu.factoryid = f.factoryid" & vbCrLf & _
                                "LEFT JOIN holdreason hr ON hr.holdreasonid = c.holdreasonid" & vbCrLf & _
                                "LEFT JOIN spec s ON s.specid = cu.specid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                "WHERE 1 = 1" & vbCrLf & _
                                "AND c.status = 1" & vbCrLf & _
                                "AND c.parentcontainerid IS NULL" & vbCrLf & _
                                "AND c.holdreasonid IS NOT NULL" & vbCrLf

        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND (f.factoryname = '" & para("FactoryName").ToString & "' OR f.factoryname IS NULL)" & vbCrLf
        End If

        strQuery += "ORDER BY c.containername DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取待接收的ERP生产任务"
    Function GetNotReadInventTask(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetNotReadInventTask
        Dim strQuery As String = "SELECT * FROM inventtaskinfo i" & vbCrLf & _
                                "WHERE i.isread = 0" & vbCrLf

        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND i.departmentid = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        strQuery += "ORDER BY i.createmodifieddate DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取开卡的生产任务"
    Function GetMfgOrder(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetMfgOrder
        Dim strQuery As String = "" & vbCrLf & _
                                "" & vbCrLf

        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND i.departmentid = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        strQuery += "ORDER BY i.createmodifieddate DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "获取上一序为检验工序的批次"
    Function GetCheckedContainer(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetCheckedContainer
        Dim strQuery As String = "SELECT c.containername,pb.productname,c.qty,wfs.workflowstepname AS specname," & vbCrLf & _
                                "   pc.prioritycodename,c.plannedstartdate,c.plannedcompletiondate" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cu.factoryid" & vbCrLf & _
                                "WHERE c.parentcontainerid IS NULL AND c.status = 1" & vbCrLf & _
                                "AND cdo.cdoname = 'MachineMfgWorkFlow'" & vbCrLf & _
                                "AND (" & vbCrLf & _
                                "   SELECT wfslist.workflowstepname" & vbCrLf & _
                                "   FROM (" & vbCrLf & _
                                "       SELECT wfs1.workflowstepname,wfs1.workflowid,wfs1.sequence" & vbCrLf & _
                                "       FROM workflowstep wfs1" & vbCrLf & _
                                "       ORDER BY wfs1.sequence DESC) wfslist" & vbCrLf & _
                                "   WHERE wfslist.workflowid = wfs.workflowid" & vbCrLf & _
                                "   AND wfslist.sequence < wfs.sequence" & vbCrLf & _
                                "   AND ROWNUM = 1 ) LIKE '%检验%'" & vbCrLf

        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        strQuery += "ORDER BY c.containername DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "当前在总检的跟踪卡"
    Function GetLastCheckContainer(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetLastCheckContainer
        Dim strQuery As String = "SELECT c.containername,pb.productname,c.qty,wfs.workflowstepname AS specname," & vbCrLf & _
                                "   pc.prioritycodename,c.plannedstartdate,c.plannedcompletiondate" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cu.factoryid" & vbCrLf & _
                                "WHERE c.parentcontainerid IS NULL AND c.status = 1" & vbCrLf & _
                                "AND cdo.cdoname = 'MachineMfgWorkFlow'" & vbCrLf & _
                                "AND wfs.islaststep = 1" & vbCrLf

        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        strQuery += "ORDER BY c.containername DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "质量记载单信息"
    Function GetQualityRecordList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetQualityRecordList
        Dim strQuery As String = "SELECT DISTINCT c.containername,pb.productname,c.qty,sb.specname,qr.checkdate,qr.qty AS defectqty,e.fullname AS checkfullname," & vbCrLf & _
                                  "c.togetherflag" & vbCrLf & _
                                  "FROM qualityrecord qr" & vbCrLf & _
                                  "LEFT JOIN container c ON c.containerid = qr.containerid" & vbCrLf & _
                                  "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                  "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                  "LEFT JOIN spec s ON s.specid = qr.specid" & vbCrLf & _
                                  "LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid" & vbCrLf & _
                                  "LEFT JOIN employee e ON e.employeeid = qr.checkemployeeid" & vbCrLf & _
                                  "LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid" & vbCrLf & _
                                  "LEFT JOIN factory f ON f.factoryid = sv.factoryid" & vbCrLf & _
                                  "WHERE qr.recordstatus ='1'" & vbCrLf

        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If
        strQuery += "ORDER BY qr.checkdate DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            For i As Integer = 0 To DT.Rows.Count - 1
                Dim namestr As String = DT.Rows(i)("specname").ToString()
                Dim strArray As String() = namestr.Split("-")
                If strArray.Length >= 2 Then
                    DT.Rows(i)("specname") = strArray(strArray.Length - 1)
                End If
            Next
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "外协批次"
    Function GetSynergicInfoList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetSynergicInfoList
        'modify by yuxj  select DISTINCT 改为 select 20170516
        Dim strQuery As String = "SELECT c.containername,pb.productname,c.qty,e.employeename AS submitemployeename,syi.submitdate,to_char(syi.plandate,'yyyy/mm/dd') plandate ," & vbCrLf & _
                                "c.togetherflag,TRUNC(SYSDATE-plandate) overdays" & vbCrLf & _
                                "FROM synergicinfo syi" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = syi.fromfactoryid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = syi.containerid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN employee e ON e.employeeid=syi.submitemployeeid " & vbCrLf & _
                                "WHERE (c.status=1 and c.parentcontainerid is null and c.containerid<>c.containername and syi.returndate IS NULL)" & vbCrLf

        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        If para.ContainsKey("OnlyOver") Then
            strQuery += " and syi.plandate<sysdate and plandate is not null "
        End If
        strQuery += " ORDER BY syi.submitdate DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)

            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    ''' <summary>
    ''' 获取外协进度看板数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSynergicProgressKbData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetSynergicProgressKbData


        Dim sb As StringBuilder = New StringBuilder()
        Dim strsql As String =
            <string>
                SELECT  B.TOGETHERFLAG,
                        B.PRODUCTNAME,
                        B.DESCRIPTION,
                        SUM(B.QTY) ZCQTY,
                        B.ZCR,
                        B.SQR,
                        B.OUTTIME,
                        B.RETURNTIME,
                        '' KONG,
                        B.ZCDW,
                        B.CSDAYS,
                        B.PLANDATE,
                        B.SPECNAME
                    FROM (SELECT C.TOGETHERFLAG,
                                PB.PRODUCTNAME,
                                P.DESCRIPTION,
                                SY.QTY,
                                E.FULLNAME ZCR,
                                EP.FULLNAME SQR,
                                TO_CHAR(SY.SUBMITDATE, 'yyyy-mm-dd HH24') OUTTIME,
                                TO_CHAR(SY.RETURNDATE, 'yyyy-mm-dd HH24') RETURNTIME,
                                CASE
                                    WHEN FT.FACTORYNAME IS NULL THEN
                                    CU.CUSTOMERNAME
                                    WHEN FT.FACTORYNAME IS NOT NULL THEN
                                    FT.FACTORYNAME
                                END ZCDW,
                                CEIL(SYSDATE - SY.PLANDATE) CSDAYS,
                                TO_CHAR(SY.PLANDATE, 'yyyy-mm-dd') PLANDATE,
                                SUBSTR(SB.SPECNAME,
                                        INSTR(SB.SPECNAME, '-', 1) + 1,
                                        INSTR(SB.SPECNAME, '-', INSTR(SB.SPECNAME, '-', 1) + 1)) SPECNAME
                            FROM SYNERGICINFO SY
                            LEFT JOIN EMPLOYEE E
                            ON SY.SUBMITEMPLOYEEID = E.EMPLOYEEID
                            LEFT JOIN SPEC S
                            ON SY.SPECID = S.SPECID
                            LEFT JOIN SPECBASE SB
                            ON S.SPECBASEID = SB.SPECBASEID
                            LEFT JOIN SYNERGICSPECVALIDATE SV
                            ON SY.DESCRIPTION = SV.SYNERGICSPECVALIDATENAME
                            LEFT JOIN EMPLOYEE EP
                            ON SV.SUBMITEMPLOYEEID = EP.EMPLOYEEID
                            LEFT JOIN SYNERGICSPECINFO SP
                            ON SV.SYNERGICSPECVALIDATEID = SP.SYNERGICSPECVALIDATEID
                            LEFT JOIN FACTORY FT
                            ON SP.FACTORYID = FT.FACTORYID
                            LEFT JOIN CUSTOMER CU
                            ON SP.CUSTOMERID = CU.CUSTOMERID
                            LEFT JOIN CONTAINER C
                            ON SY.CONTAINERID = C.CONTAINERID
                            LEFT JOIN PRODUCT P
                            ON P.PRODUCTID = C.PRODUCTID
                            LEFT JOIN PRODUCTBASE PB
                            ON PB.PRODUCTBASEID = P.PRODUCTBASEID
                            LEFT JOIN PRODUCTFAMILY F
                            ON F.PRODUCTFAMILYID = P.PRODUCTFAMILYID
                            WHERE C.STATUS = 1
                            AND C.CONTAINERID != C.CONTAINERNAME
                            AND SYSDATE > SY.PLANDATE
                            AND SY.PLANDATE IS NOT NULL
                            AND SY.RETURNDATE IS NULL
                            AND C.PARENTCONTAINERID IS NULL
                            AND C.TOGETHERFLAG IS NOT NULL) B
                    GROUP BY B.TOGETHERFLAG,
                            B.PRODUCTNAME,
                            B.OUTTIME,
                            B.RETURNTIME,
                            B.DESCRIPTION,
                            B.ZCR,
                            B.SQR,
                            B.ZCDW,
                            B.CSDAYS,
                            B.PLANDATE,
                            B.SPECNAME
                    ORDER BY B.TOGETHERFLAG desc
            </string>
        sb.AppendLine(strsql)
        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(sb.ToString)

            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result

    End Function






#End Region

#Region "发料不足的跟踪卡"
    Function GetNotEnoughList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetNotEnoughList
        Dim strQuery As String = "SELECT c.containername,pb.productname,c.qty,wfs.workflowstepname AS specname,c.plannedstartdate,c.plannedcompletiondate," & vbCrLf & _
                                "   (" & vbCrLf & _
                                "       SELECT datacoll.datavalue" & vbCrLf & _
                                "       FROM (" & vbCrLf & _
                                "           SELECT hml.containerid,dd.dataname,dd.datavalue" & vbCrLf & _
                                "           FROM datapointhistorydetail dd" & vbCrLf & _
                                "           LEFT JOIN datapointhistory dh ON dh.datapointhistoryid = dd.datapointhistoryid" & vbCrLf & _
                                "           LEFT JOIN historymainline hml ON hml.historymainlineid = dh.historymainlineid" & vbCrLf & _
                                "           WHERE hml.reversalstatus = 1 AND dd.dataname = '实供数量'" & vbCrLf & _
                                "           ORDER BY hml.txndate DESC" & vbCrLf & _
                                "       ) datacoll WHERE datacoll.containerid = c.containerid AND ROWNUM = 1" & vbCrLf & _
                                "   ) AS materialqty" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cu.factoryid" & vbCrLf & _
                                "LEFT JOIN materialtype mt ON mt.materialtypeid = p.materialtypeid" & vbCrLf & _
                                "WHERE c.status = 1 AND c.parentcontainerid IS NULL" & vbCrLf & _
                                "AND (mt.materialtypename = '金属' OR mt.materialtypename = '非金属')" & vbCrLf

        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        strQuery += "ORDER BY c.containername DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "转总检处理的批次"
    Function GetToLastCheck(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetToLastCheck
        Dim strQuery As String = "SELECT DISTINCT c.containername,pb.productname,c.qty,wfs.workflowstepname AS specname," & vbCrLf & _
                                "   pc.prioritycodename,c.plannedstartdate,c.plannedcompletiondate" & vbCrLf & _
                                "FROM productnoinfo pi" & vbCrLf & _
                                "LEFT JOIN qualityrecord qr ON qr.qualityrecordid = pi.qualityrecordid" & vbCrLf & _
                                "LEFT JOIN container c ON c.containerid = qr.containerid" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "LEFT JOIN qualitydisposeadvice qd ON qd.qualitydisposeadviceid = pi.qualitydisposeadviceid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cu.factoryid" & vbCrLf & _
                                "WHERE qd.qualitydisposeadvicename = '转总检处理'" & vbCrLf & _
                                "AND c.status = 1 AND c.parentcontainerid IS NULL" & vbCrLf

        If para.ContainsKey("FactoryName") = True Then
            strQuery += "AND f.factoryname = '" & para("FactoryName").ToString & "'" & vbCrLf
        End If

        strQuery += "ORDER BY c.containername DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "当前在领料流程的批次"
    Function GetMaterialContainer(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetMaterialContainer
        Dim strQuery As String = "SELECT DISTINCT c.containername,pb.productname,c.qty,sb.specname," & vbCrLf & _
                                "pc.prioritycodename,c.plannedstartdate,c.plannedcompletiondate,c.togetherflag,TRUNC(SYSDATE-plannedcompletiondate) overdays" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.revofrcdid=cu.specid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cu.factoryid" & vbCrLf & _
                                "WHERE c.status = 1 AND c.parentcontainerid IS NULL and togetherflag is not null " & vbCrLf & _
                                "AND cdo.cdoname = 'Workflow'"
        If para.ContainsKey("OnlyOver") Then
            strQuery += " and plannedcompletiondate<sysdate "
        End If
        strQuery += "ORDER BY c.containername DESC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            For i As Integer = 0 To DT.Rows.Count - 1
                Dim namestr As String = DT.Rows(i)("specname").ToString()
                Dim strArray As String() = namestr.Split("-")
                If strArray.Length >= 2 Then
                    DT.Rows(i)("specname") = strArray(strArray.Length - 1)
                End If
            Next
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    Function GetMaterialContainerProcess(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetMaterialContainerProcess
        Dim strQuery As String = "SELECT pb.productname,sum(c.qty) qty,sb.specname," & vbCrLf & _
                                "pc.prioritycodename,c.plannedstartdate,c.plannedcompletiondate,c.togetherflag,TRUNC(SYSDATE-plannedcompletiondate) overdays" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "LEFT JOIN specbase sb ON sb.revofrcdid=cu.specid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cu.factoryid" & vbCrLf & _
                                "WHERE c.status = 1 AND c.parentcontainerid IS NULL and togetherflag is not null " & vbCrLf & _
                                "AND cdo.cdoname = 'Workflow'"
        If para.ContainsKey("OnlyOver") Then
            strQuery += " and plannedcompletiondate<sysdate "
        End If
        strQuery += " group by pb.productname,sb.specname,pc.prioritycodename,c.plannedstartdate,c.plannedcompletiondate,c.togetherflag,TRUNC(SYSDATE-plannedcompletiondate)"
        strQuery += "ORDER BY c.togetherflag DESC"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            For i As Integer = 0 To DT.Rows.Count - 1
                Dim namestr As String = DT.Rows(i)("specname").ToString()
                Dim strArray As String() = namestr.Split("-")
                If strArray.Length >= 2 Then
                    DT.Rows(i)("specname") = strArray(strArray.Length - 1)
                End If
            Next
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

#Region "当前已完成领料流程的批次"
    Function GetMaterialedContainer(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMESMainViewIBusiness.GetMaterialedContainer
        Dim strQuery As String = "SELECT DISTINCT c.containername,pb.productname,c.qty,wfs.workflowstepname AS specname," & vbCrLf & _
                                "   pc.prioritycodename,c.plannedstartdate,c.plannedcompletiondate" & vbCrLf & _
                                "FROM container c" & vbCrLf & _
                                "LEFT JOIN product p ON p.productid = c.productid" & vbCrLf & _
                                "LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid" & vbCrLf & _
                                "LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid" & vbCrLf & _
                                "LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid" & vbCrLf & _
                                "LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                                "LEFT JOIN prioritycode pc ON pc.prioritycodeid = c.prioritycodeid" & vbCrLf & _
                                "LEFT JOIN factory f ON f.factoryid = cu.factoryid" & vbCrLf & _
                                "WHERE c.status = 1 AND c.parentcontainerid IS NULL" & vbCrLf & _
                                "AND cdo.cdoname = 'MachineMfgWorkFlow'" & vbCrLf & _
                                "AND (" & vbCrLf & _
                                "   SELECT hml.historymainlineid" & vbCrLf & _
                                "   FROM historymainline hml" & vbCrLf & _
                                "   LEFT JOIN workflowstep wfs ON wfs.workflowstepid = hml.workflowstepid" & vbCrLf & _
                                "   LEFT JOIN workflow wf ON wf.workflowid = wfs.workflowid" & vbCrLf & _
                                "   LEFT JOIN cdodefinition cdo ON cdo.cdodefid = wf.cdotypeid" & vbCrLf & _
                                "   WHERE hml.reversalstatus = 1 AND cdo.cdoname = 'Workflow'" & vbCrLf & _
                                "   AND hml.containerid = c.containerid AND ROWNUM = 1" & vbCrLf & _
                                ") IS NOT NULL" & vbCrLf & _
                                "ORDER BY c.containername DESC" & vbCrLf

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetMainViewInfo(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region
End Class
